### ATTENTION: DO NOT TO ATTEMPT TO RUN THIS NOTEBOOK IF YOU DO NOT HAVE ALL DATA CONNECTED TO IT, AS IT WOULD RESULT INTO AN ERROR! ###
# Skip this block if you are running on local computer
# connection with google drive
from google.colab import drive
drive.mount('/content/drive')

Mounted at /content/drive

# import libraries and modules needed for the project
from PIL import Image
import pandas as pd   # !pip install pandas pyarrow
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import folium
import geopandas as gpd  # !pip install geopandas
import requests   # !pip install requests
import calendar
import os
from PIL import Image
import matplotlib.pyplot as plt
from google.colab import files
from folium.plugins import MarkerCluster
!pip install adjustText
from adjustText import adjust_text
from shapely.geometry import Point  # merge geodata

Requirement already satisfied: adjustText in /usr/local/lib/python3.10/dist-packages (1.0.4)
Requirement already satisfied: numpy in /usr/local/lib/python3.10/dist-packages (from adjustText) (1.25.2)
Requirement already satisfied: matplotlib in /usr/local/lib/python3.10/dist-packages (from adjustText) (3.7.1)
Requirement already satisfied: scipy in /usr/local/lib/python3.10/dist-packages (from adjustText) (1.11.4)
Requirement already satisfied: contourpy>=1.0.1 in /usr/local/lib/python3.10/dist-packages (from matplotlib->adjustText) (1.2.0)
Requirement already satisfied: cycler>=0.10 in /usr/local/lib/python3.10/dist-packages (from matplotlib->adjustText) (0.12.1)
Requirement already satisfied: fonttools>=4.22.0 in /usr/local/lib/python3.10/dist-packages (from matplotlib->adjustText) (4.49.0)
Requirement already satisfied: kiwisolver>=1.0.1 in /usr/local/lib/python3.10/dist-packages (from matplotlib->adjustText) (1.4.5)
Requirement already satisfied: packaging>=20.0 in /usr/local/lib/python3.10/dist-packages (from matplotlib->adjustText) (23.2)
Requirement already satisfied: pillow>=6.2.0 in /usr/local/lib/python3.10/dist-packages (from matplotlib->adjustText) (9.4.0)
Requirement already satisfied: pyparsing>=2.3.1 in /usr/local/lib/python3.10/dist-packages (from matplotlib->adjustText) (3.1.2)
Requirement already satisfied: python-dateutil>=2.7 in /usr/local/lib/python3.10/dist-packages (from matplotlib->adjustText) (2.8.2)
Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.10/dist-packages (from python-dateutil>=2.7->matplotlib->adjustText) (1.16.0)

# Open the image file
image = Image.open('/content/drive/MyDrive/ForestNetDataset/Architecture diagram.png')

# Set the figure size to a larger value to make the displayed image bigger
plt.figure(figsize=(15, 10))  # Adjust the width and height as needed

# Display the image
plt.imshow(image)

# Remove the axis for a cleaner look
plt.axis('off')

# Show the plot
plt.show()

# Initially, the individual data files comprising the ForestNet dataset are imported.
# import dataframes /content/drive/MyDrive/ForestNetDataset
path_train = '/content/drive/MyDrive/ForestNetDataset/data_forest_tabular/train.csv'
path_test = '/content/drive/MyDrive/ForestNetDataset/data_forest_tabular/test.csv'
path_val = '/content/drive/MyDrive/ForestNetDataset/data_forest_tabular/val.csv'

# Read the files
df_train = pd.read_csv(path_train)
df_test = pd.read_csv(path_test)
df_val = pd.read_csv(path_val)

# add a column to each dataframe so that we know their origin
df_train['origin'] = 'train'
df_test['origin'] = 'test'
df_val['origin'] = 'val'

# for better usability merge all dataframes together
df = pd.concat([df_train, df_test, df_val], ignore_index=True)

# Displaying an example of how the data looks
df.head()

# map centered around the approximate center of Indonesia
map = folium.Map(location=[-2.2151, 115.6628], zoom_start=5)

# Add points to the map
for idx, row in df.iterrows():
    folium.CircleMarker(location=[row['latitude'], row['longitude']],
                        radius=3,
                        fill=True).add_to(map)

# Display the map
map

# Load the regions dataset as a GeoDataFrame
regions = gpd.read_file('/content/drive/MyDrive/ForestNetDataset/data_provinces/BATAS_PROVINSI_DESEMBER_2019_DUKCAPIL.shp')

# Convert the DataFrame to a GeoDataFrame
# to be able to then match the coordinates with the regions
gdf = gpd.GeoDataFrame( # converting the df to geodf
    df, geometry = [Point(xy) for xy in zip(df.longitude, df.latitude)])

gdf.head()

# Display the initial number of rows in the regions dataset
num_rows = regions.shape[0]
print(f"The number of Provinces is: {num_rows}")

The number of Provinces is: 34

# Perform the spatial join to match deforestation points with their corresponding provinces
gdf = gpd.sjoin(gdf, regions, how="left", op='within')

/usr/local/lib/python3.10/dist-packages/IPython/core/interactiveshell.py:3473: FutureWarning: The `op` parameter is deprecated and will be removed in a future release. Please use the `predicate` parameter instead.
  if (await self.run_code(code, result,  async_=asy)):
<ipython-input-16-316c481b0557>:2: UserWarning: CRS mismatch between the CRS of left geometries and the CRS of right geometries.
Use `to_crs()` to reproject one of the input geometries to match the CRS of the other.

Left CRS: None
Right CRS: EPSG:4326

  gdf = gpd.sjoin(gdf, regions, how="left", op='within')

# Rename the 'PROVINSI' column to 'province' for clarity
gdf = gdf.rename(columns={'PROVINSI': 'province'})
gdf.head()

# Display the initial number of rows after the join
num_rows = gdf.shape[0]
print(f"After the join, the number of rows is: {num_rows}")

After the join, the number of rows is: 2757

# Drop columns not needed
gdf.drop(['index_right', 'OBJECTID', 'Shape_Leng', 'Shape_Area', 'geometry'], axis=1, inplace=True)

# Make all the regions name lower case for clarirty
gdf['province'] = gdf['province'].str.lower()
gdf.head()

# Regions Map
indonesia_map = regions
indonesia_map['province'] = indonesia_map['PROVINSI'].str.lower()

# Aggregate data points by province
province_counts = gdf['province'].value_counts().reset_index()
province_counts.columns = ['province', 'data_point_count']

# Sort and get the top five provinces
top_provinces = province_counts.nlargest(5, 'data_point_count')

# Merge the shapefile with your data points count
map_with_data = indonesia_map.merge(top_provinces, on='province', how='left')

# Plot the map with all provinces
fig, ax = plt.subplots(1, 1, figsize=(15, 10))
indonesia_map.plot(ax=ax, color='lightgrey')  # Plot all provinces in a light grey color

# Now plot the provinces with data on top
map_with_data.plot(column='data_point_count', ax=ax, legend=True,
                   legend_kwds={'label': "Number of Data Points by Province",
                                'orientation': "horizontal"},
                   cmap='YlGn')

# Annotate only the top five provinces
for idx, row in map_with_data.iterrows():
    if pd.notnull(row['data_point_count']) and row['province'] in top_provinces['province'].values:
        plt.annotate(text=row['province'], xy=(row['geometry'].centroid.x, row['geometry'].centroid.y),
                     horizontalalignment='center', fontsize=10, color='black')

plt.title("Top 5 Regions with most data points about deforestation", fontdict=None, loc='center', pad=None)

# Remove the axis for a cleaner look and display the map
plt.axis('off')
plt.show()

# Retrieve API key from environment variables
API_KEY = os.getenv('VISUAL_CROSSING_API_KEY')
# Check if API key is available, if not raise an error
if not API_KEY:
    raise ValueError("No API Key found. Set the VISUAL_CROSSING_API_KEY environment variable.")

# Base URL for Visual Crossing Weather API
BASE_URL = 'https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/'

# Define the range of years for which to fetch weather data
start_year = 2002
end_year = 2016

# Initialize an empty list to store the data
data = []

# Define the list of provinces with their names and geographic coordinates
provinces = [
    {"name": "Riau", "latitude": 0.5004112, "longitude": 101.5475811},
    {"name": "Central Kalimantan", "latitude": -1.499583, "longitude": 113.2903307},
    {"name": "West Kalimantan", "latitude": -0.1322387, "longitude": 111.0968901},
    {"name": "Jambi", "latitude": -1.611572, "longitude": 102.779699},
    {"name": "East Kalimantan", "latitude": 0.7884397, "longitude": 116.2419977},
    {"name": "North Sumatra", "latitude": 2.1923519, "longitude": 99.3812201},
    {"name": "North Kalimantan", "latitude": 3.0235817, "longitude": 116.2049306},
    {"name": "Papua", "latitude": -3.9885728, "longitude": 138.3485219},
    {"name": "Central Sulawesi", "latitude": -1.6937786, "longitude": 120.8088555},
    {"name": "West Sumatra", "latitude": -0.5827529, "longitude": 100.6133379},
    {"name": "Aceh", "latitude": 4.3685491, "longitude": 97.0253024},
    {"name": "Southeast Sulawesi", "latitude": -3.5491199, "longitude": 121.7279646},
    {"name": "South Sumatra", "latitude": -3.1266842, "longitude": 104.0930554},
    {"name": "West Papua", "latitude": -1.3842356, "longitude": 132.902528},
    {"name": "North Maluku", "latitude": 0.6301215, "longitude": 127.9720219},
    {"name": "Bangka Belitung Islands", "latitude": -2.7410513, "longitude": 106.4405872},
    {"name": "South Kalimantan", "latitude": -2.9285686, "longitude": 115.3700718},
    {"name": "West Sulawesi", "latitude": -2.4974546, "longitude": 119.3918955},
    {"name": "South Sulawesi", "latitude": -3.6446718, "longitude": 119.9471906},
    {"name": "Bengkulu", "latitude": -3.5186763, "longitude": 102.5359834},
    {"name": "Maluku", "latitude": -3.118837, "longitude": 129.4207759},
    {"name": "Gorontalo", "latitude": 0.7186174, "longitude": 122.4555927},
    {"name": "North Sulawesi", "latitude": 0.6555692, "longitude": 124.090151},
    {"name": "Lampung", "latitude": -4.8555039, "longitude": 105.0272986},
    {"name": "East Java", "latitude": -7.6977397, "longitude": 112.4914199},
    {"name": "Riau Islands", "latitude": -0.1547846, "longitude": 104.5803745},
    {"name": "West Java", "latitude": -6.8891904, "longitude": 107.6404716},
    {"name": "Banten", "latitude": -6.4453801, "longitude": 106.1375586},
    {"name": "West Nusa Tenggara", "latitude": -8.6529334, "longitude": 117.3616476},
]

# Loop over each province and year to fetch weather data
for province in provinces:
    for year in range(start_year, end_year + 1):
        start_date = f"{year}-01-01"
        end_date = f"{year}-12-31"

        # Create the location string from the latitude and longitude
        location = f"{province['latitude']},{province['longitude']}"
        # Construct the URL for the API request
        url = f"{BASE_URL}{location}/{start_date}/{end_date}?unitGroup=metric&key={API_KEY}&include=days"

        # Make the API request
        response = requests.get(url)
        # Check if the request was successful
        if response.status_code == 200:
            yearly_data = response.json()
            # Calculate the total rainfall for the year
            total_rainfall = sum(day.get('precip', 0) for day in yearly_data.get('days', []) if day.get('precip') is not None)

            # Calculate the average temperature for the year
            valid_temps = [day.get('temp') for day in yearly_data.get('days', []) if day.get('temp') is not None]
            if valid_temps:  # Check if the list is not empty
                avg_temp = sum(valid_temps) / len(valid_temps)
            else:
                avg_temp = None  # Placeholder value indicating no data

            # Append the results to the data list
            data.append({
                "Province": province['name'],
                "Year": year,
                "Average Temperature (°C)": avg_temp,
                "Total Rainfall (mm)": total_rainfall,
            })
            print(f"Processed {province['name']} for {year}")
        else:
            print(f"Failed to retrieve data for {province['name']} for {year}. Status code: {response.status_code}")

# Convert the data list into a pandas DataFrame
df = pd.DataFrame(data)

# Group by province and year, then calculate mean temperature and rainfall
grouped = df.groupby(['Province', 'Year']).agg({
    'Average Temperature (°C)': 'mean',
    'Total Rainfall (mm)': 'mean'
}).reset_index()

# Pivot the table for average temperature
avg_temp_pivot = grouped.pivot(index='Province', columns='Year', values='Average Temperature (°C)')
avg_temp_pivot.columns = [f'{col} Average Temp' for col in avg_temp_pivot.columns]

# Pivot the table for total rainfall
avg_rain_pivot = grouped.pivot(index='Province', columns='Year', values='Total Rainfall (mm)')
avg_rain_pivot.columns = [f'{col} Average Rain' for col in avg_rain_pivot.columns]

# Join the pivoted tables
result = avg_temp_pivot.join(avg_rain_pivot).reset_index()

# Define the CSV file path where the result will be saved
csv_file_path = "data_weather/historical_weather_data_annual.csv"
# Save the result to a CSV file
result.to_csv(csv_file_path, index=False)

# Print completion message with the CSV file path
print(f"Data saved to {csv_file_path}")

#Change to parquet file
uploaded = files.upload()

csv_file = 'historical_weather_data_annual.csv'
df = pd.read_csv(csv_file)

parquet_file = 'historical_weather_data_annual.parquet'
df.to_parquet(parquet_file, index=False)

files.download(parquet_file)

# Extract the different years from the dataset
unique_years = df['year'].unique()

# Put the years in ascending order
unique_years.sort()

# Count the number of entries for each year
entry_counts = [len(df[df['year'] == year]) for year in unique_years]

# Plotting
plt.figure(figsize=(10, 6), facecolor = 'none', edgecolor='none')
plt.bar(unique_years, entry_counts, color='skyblue')
plt.xlabel('Year')
plt.ylabel('Number of Entries')
plt.title('Number of Images for Each Year')
plt.grid(False)
plt.xticks(unique_years, rotation=45)
plt.tight_layout()
plt.show()

#Calculating the total number of unique data points

# Create a set of tuples where each tuple is a unique location (latitude, longitude)
unique_locations = set(zip(df['latitude'], df['longitude']))

# Convert the set back to a list
unique_locations_list = list(unique_locations)

# Print the first few locations to verify
#print(unique_locations_list[:5])

# Count the number of unique locations on the dataframe
number_of_unique_locations = len(unique_locations)

# Display the number of unique locations
print("Total unique locations:", number_of_unique_locations)

Total unique locations: 2757

# Calculate the frequency distribution of the 'label' column
label_counts = df['label'].value_counts().sort_values(ascending=True)  # Sort from smallest to largest

# Plotting the frequency distribution as a horizontal bar chart
plt.figure(figsize=(8, 6))
ax = label_counts.plot(kind='barh')  # Use 'barh' for horizontal bar chart
plt.title('Frequency Distribution of the Causes of Deforestation')
plt.xlabel('Frequency')
plt.ylabel('Label')
ax.set_facecolor('none')  # Set the axes background to transparent

# Iterate over the bars and add text labels
for i, v in enumerate(label_counts):
    ax.text(v + 1, i, str(v), color='black', va='center')

plt.show()

# Causes and locations

# Create a base map
map = folium.Map(location=[df['latitude'].mean(), df['longitude'].mean()], zoom_start=5, tiles='cartodbpositron')

# MarkerCluster to cluster close markers together
marker_cluster = MarkerCluster().add_to(map)

# Define a fixed list of colors
colors = ['red', 'blue', 'green', 'purple', 'orange', 'darkred', 'lightred', 'beige', 'darkblue', 'darkgreen', 'cadetblue', 'darkpurple', 'white', 'pink', 'lightblue', 'lightgreen', 'gray', 'black', 'lightgray']

# Generate a color for each unique 'merged_label'
unique_labels = df['merged_label'].unique()
color_dict = {label: colors[i % len(colors)] for i, label in enumerate(unique_labels)}

# Add markers to the map, ensuring different colors for each 'merged_label'
for _, row in df.iterrows():
    icon_color = color_dict.get(row['merged_label'], 'lightgray')  # Default to 'lightgray' if no match
    folium.Marker(
        location=[row['latitude'], row['longitude']],
        popup=f"Cause: {row['merged_label']}",
        icon=folium.Icon(color=icon_color)
    ).add_to(marker_cluster)

# Display the map
map

# Read the CSV file into a DataFrame
weather_data = pd.read_csv("/content/drive/My Drive/ForestNetDataset/data_weather/combined_weather_data.csv")

weather_data.head()

weather_data.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 464 entries, 0 to 463
Data columns (total 4 columns):
 #   Column        Non-Null Count  Dtype  
---  ------        --------------  -----  
 0   province      464 non-null    object 
 1   year          464 non-null    int64  
 2   average_temp  367 non-null    float64
 3   average_rain  464 non-null    float64
dtypes: float64(2), int64(1), object(1)
memory usage: 14.6+ KB

weather_data.nunique()

province         29
year             16
average_temp    367
average_rain    355
dtype: int64

# Drop rows with missing values for now
weather_data_cleaned = weather_data.dropna()

# Taking the average temperature of each province and sorting
province_grouped_temp = weather_data_cleaned.groupby('province').mean().reset_index().sort_values(by='average_temp', ascending=False)

plt.figure(figsize=(8, 6))
palette_reversed = sns.color_palette("coolwarm", len(province_grouped_temp))[::-1]
sns.barplot(x='average_temp', y='province', data=province_grouped_temp, palette=palette_reversed)
plt.title('Average Temperature by Province')
plt.xlabel('Average Temperature (Degrees Celcius)')
plt.ylabel('Province')
plt.show()

<ipython-input-33-dcb35ffb2a3e>:9: FutureWarning: 

Passing `palette` without assigning `hue` is deprecated and will be removed in v0.14.0. Assign the `y` variable to `hue` and set `legend=False` for the same effect.

  sns.barplot(x='average_temp', y='province', data=province_grouped_temp, palette=palette_reversed)

# Taking the average rainfall of each province and sorting
province_grouped_rain = weather_data_cleaned.groupby('province').mean().reset_index().sort_values(by='average_rain', ascending=False)

plt.figure(figsize=(8, 6))
sns.barplot(x='average_rain', y='province', data=province_grouped_rain, palette='coolwarm')
plt.title('Average Rainfall by Province')
plt.xlabel('Average Rainfall (mm)')
plt.ylabel('Province')
plt.show()

<ipython-input-19-da9c73509f8a>:5: FutureWarning: 

Passing `palette` without assigning `hue` is deprecated and will be removed in v0.14.0. Assign the `y` variable to `hue` and set `legend=False` for the same effect.

  sns.barplot(x='average_rain', y='province', data=province_grouped_rain, palette='coolwarm')

# Grouping by Year
year_grouped = weather_data_cleaned.groupby('year').mean().reset_index()

# Visualising trends over the years
plt.figure(figsize=(12, 6))
plt.subplot(1, 2, 1)
sns.lineplot(x='year', y='average_temp', data=year_grouped, marker='o', color='skyblue')
plt.title('Average Temperature by Year')
plt.xticks(rotation=45)
plt.ylabel('Average Temperature (Degrees Celcius)')

plt.subplot(1, 2, 2)
sns.lineplot(x='year', y='average_rain', data=year_grouped, marker='o', color='salmon')
plt.title('Average Rainfall by Year')
plt.xticks(rotation=45)
plt.yticks(np.arange(1500, max(year_grouped['average_rain'])+100, 500))
plt.ylabel('Average Rainfall (mm)')

plt.tight_layout()
plt.show()

<ipython-input-21-cafddd92c241>:2: FutureWarning: The default value of numeric_only in DataFrameGroupBy.mean is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function.
  year_grouped = weather_data_cleaned.groupby('year').mean().reset_index()

# Calculate the mean values for each province
province_means = weather_data.groupby('province').mean()

# Create the scatter plot with averaged values
plt.figure(figsize=(10, 8))
scatter = plt.scatter(x=province_means['average_temp'], y=province_means['average_rain'], s=100, alpha=0.8, color='blue')

# Add labels and title
plt.xlabel('Average Temperature (Degrees Celcius)', fontsize=12)
plt.ylabel('Average Rainfall (mm)', fontsize=12)
plt.title('Average Temperature vs. Average Rainfall by Province', fontsize=14)

# Add province names as annotations
texts = [plt.text(province_means['average_temp'][i], province_means['average_rain'][i], province_means.index[i], ha='center', va='center', fontsize=10) for i in range(len(province_means))]

# Adjust label positions to minimize overlap and set distance between label and point
adjust_text(texts, force_text=(0.5, 0.5), force_static=(0.5, 0.5), force_pull=(0.1, 0.1), force_explode=(0.5, 0.5))

# Remove top and right spines
plt.gca().spines['top'].set_visible(False)
plt.gca().spines['right'].set_visible(False)

plt.show()

# The buckets were created
image_1 = Image.open('/content/drive/MyDrive/ForestNetDataset/Screenshots/buckets1.png')
# Set the figure size to a larger value to make the displayed image bigger
plt.figure(figsize=(20, 10))  # Adjust the width and height as needed
# Remove the axis for a cleaner look
plt.axis('off')
# Display the image
plt.imshow(image_1)

<matplotlib.image.AxesImage at 0x7cf47bba90f0>

# Open the image file
forestnet = Image.open('/content/drive/MyDrive/ForestNetDataset/Screenshots/forestnet.png')
# Set the figure size to a larger value to make the displayed image bigger
plt.figure(figsize=(20, 10))  # Adjust the width and height as needed

plt.xticks([])
plt.yticks([])

# Display the image
plt.imshow(forestnet)

<matplotlib.image.AxesImage at 0x7cf47bc83a60>

image_3 = Image.open('/content/drive/MyDrive/ForestNetDataset/Screenshots/Picture3.1.png')

plt.figure(figsize=(20, 10))

plt.xticks([])
plt.yticks([])

plt.imshow(image_3)

<matplotlib.image.AxesImage at 0x7cf47bd19ab0>

#Deforestion table has been populated succesfully

deforestation_data = Image.open('/content/drive/MyDrive/ForestNetDataset/Screenshots/deforestation_data.png')
# Set the figure size to a larger value to make the displayed image bigger

plt.axis('off')
# Display the image
plt.imshow(deforestation_data)

<matplotlib.image.AxesImage at 0x7d0ece6628c0>

#Weather table has been populated succesfully

weather_data = Image.open('/content/drive/MyDrive/ForestNetDataset/Screenshots/weather_data.png')
# Set the figure size to a larger value to make the displayed image bigger

plt.axis('off')
# Display the image
plt.imshow(weather_data)

<matplotlib.image.AxesImage at 0x7d0ece663ca0>

merged_deforestation_weather= Image.open('/content/drive/MyDrive/ForestNetDataset/Screenshots/merged_deforestation_weather.png')
plt.axis('off')
plt.figure(figsize=(15, 10))
plt.imshow(merged_deforestation_weather)

<matplotlib.image.AxesImage at 0x7d0ece3f01c0>

dashboard1= Image.open('/content/drive/MyDrive/ForestNetDataset/dashboard1.png')
plt.figure(figsize=(15, 10))
plt.axis('off')
plt.imshow(dashboard1)

<matplotlib.image.AxesImage at 0x7d0ecbf4b190>

from PIL import Image
bq1 = Image.open('/content/drive/MyDrive/ForestNetDataset/Screenshots/bigq1.png')
# Set the figure size to a larger value to make the displayed image bigger

plt.axis('off')
# Display the image
plt.imshow(bq1)

<matplotlib.image.AxesImage at 0x7c0ebd45b760>

# Source connection profile
bq2 = Image.open('/content/drive/MyDrive/ForestNetDataset/Screenshots/bigq2.png')
# Set the figure size to a larger value to make the displayed image bigger

plt.axis('off')
# Display the image
plt.imshow(bq2)

<matplotlib.image.AxesImage at 0x78ad5f190b80>

# Destination connection profile
bq3 = Image.open('/content/drive/MyDrive/ForestNetDataset/Screenshots/bigq3.png')
# Set the figure size to a larger value to make the displayed image bigger

plt.axis('off')
# Display the image
plt.imshow(bq3)

<matplotlib.image.AxesImage at 0x78ad5cffc430>

# Defining stream details
bq4 = Image.open('/content/drive/MyDrive/ForestNetDataset/Screenshots/bigq4.png')
# Set the figure size to a larger value to make the displayed image bigger

plt.axis('off')
# Display the image
plt.imshow(bq4)

<matplotlib.image.AxesImage at 0x7c0eee01d960>

# Replication slot
replication = Image.open('/content/drive/MyDrive/ForestNetDataset/Screenshots/replicationslot.png')
# Set the figure size to a larger value to make the displayed image bigger

plt.axis('off')
# Display the image
plt.imshow(publication)

<matplotlib.image.AxesImage at 0x7c0ebd525e40>

# Source Configuration
source= Image.open('/content/drive/MyDrive/ForestNetDataset/Screenshots/source-bq.png')
# Set the figure size to a larger value to make the displayed image bigger

plt.axis('off')
# Display the image
plt.imshow(source)

<matplotlib.image.AxesImage at 0x7d0ec9b9b4c0>

# Destination Configuration
destination= Image.open('/content/drive/MyDrive/ForestNetDataset/Screenshots/destination-bq.png')
# Set the figure size to a larger value to make the displayed image bigger

plt.axis('off')
# Display the image
plt.imshow(destination)

<matplotlib.image.AxesImage at 0x7d0ecbf4a380>

# Running validation to create data stream
validation_bq = Image.open('/content/drive/MyDrive/ForestNetDataset/Screenshots/validation_bq.png')
# Set the figure size to a larger value to make the displayed image bigger

plt.axis('off')
# Display the image
plt.imshow(validation_bq)

<matplotlib.image.AxesImage at 0x7c0eb897d870>

gsutil cat gs://ucl-weather/historical_weather_data_top3.csv | tail -n +2 | gsutil cp - gs://ucl-weather/historical_weather_data_top3_no_headers.csv
gcloud sql import csv forestnet-data gs://ucl-weather/historical_weather_data_top3_no_headers.csv --database=postgres --table=weather_data --project=engineering-group-project```

### WORD COUNT ###
import nbformat

# Load the current notebook
notebook_path = '/content/drive/MyDrive/ForestNetDataset/DE_Forest_Net_Draft.ipynb'
with open(notebook_path, 'r', encoding='utf-8') as nb_file:
    nb_contents = nbformat.read(nb_file, as_version=4)

# Initialize word count
total_word_count = 0

# Iterate through each cell in the notebook
for cell in nb_contents['cells']:
    if cell['cell_type'] == 'markdown':
        total_word_count += len(cell['source'].split())
    elif cell['cell_type'] == 'code':
        # Count words in code comments
        comments = [line for line in cell['source'].split('\n') if line.strip().startswith('#')]
        comment_text = ' '.join(comments)
        total_word_count += len(comment_text.split())

# Print the total word count
print(f"Total Word Count: {total_word_count}")

Total Word Count: 5080

image_names = [
    "-0.002226324002905_109.97159881327198",
    "-0.025186616919319_113.71975529073988",
    "-0.03027428009349_101.59766149324824",
    "-0.22946911191326_101.62682788208392",
    "-0.23142888714857_99.87426629402542"
]

# Function to find matching row in DataFrame for given image name
def find_matching_row(gdf, image_name):
    # Split image name into latitude and longitude
    lat_str, long_str = image_name.split('_')
    latitude = float(lat_str)
    longitude = float(long_str)

    # Find row in DataFrame where latitude and longitude match
    match = df[(df['latitude'].astype(str).str.contains(lat_str)) & (df['longitude'].astype(str).str.contains(long_str))]
    return match

# Iterate through image names and find matching rows
matches = []
for image_name in image_names:
    match = find_matching_row(df, image_name)
    if not match.empty:
        matches.append((image_name, match.index.tolist()))

matches

[('-0.002226324002905_109.97159881327198', [392]),
 ('-0.025186616919319_113.71975529073988', [2502]),
 ('-0.03027428009349_101.59766149324824', [1513]),
 ('-0.22946911191326_101.62682788208392', [162]),
 ('-0.23142888714857_99.87426629402542', [2584])]

	label	merged_label	latitude	longitude	year	example_path	origin
0	Timber plantation	Plantation	4.430849	96.101634	2014	examples/4.430849118860583_96.1016343478138	train
1	Other	Other	1.332341	109.374229	2012	examples/1.3323406178609702_109.37422873130464	train
2	Grassland shrubland	Grassland shrubland	-1.720266	115.006996	2016	examples/-1.720266384577504_115.00699582064485	train
3	Small-scale agriculture	Smallholder agriculture	-2.248346	104.135786	2011	examples/-2.248346072674411_104.1357857482906	train
4	Other large-scale plantations	Plantation	-2.100800	113.022376	2008	examples/-2.100800102991412_113.0223763234016	train

	label	merged_label	latitude	longitude	year	example_path	origin	geometry
0	Timber plantation	Plantation	4.430849	96.101634	2014	examples/4.430849118860583_96.1016343478138	train	POINT (96.10163 4.43085)
1	Other	Other	1.332341	109.374229	2012	examples/1.3323406178609702_109.37422873130464	train	POINT (109.37423 1.33234)
2	Grassland shrubland	Grassland shrubland	-1.720266	115.006996	2016	examples/-1.720266384577504_115.00699582064485	train	POINT (115.00700 -1.72027)
3	Small-scale agriculture	Smallholder agriculture	-2.248346	104.135786	2011	examples/-2.248346072674411_104.1357857482906	train	POINT (104.13579 -2.24835)
4	Other large-scale plantations	Plantation	-2.100800	113.022376	2008	examples/-2.100800102991412_113.0223763234016	train	POINT (113.02238 -2.10080)

	label	merged_label	latitude	longitude	year	example_path	origin	geometry	index_right	OBJECTID	province	Shape_Leng	Shape_Area
0	Timber plantation	Plantation	4.430849	96.101634	2014	examples/4.430849118860583_96.1016343478138	train	POINT (96.10163 4.43085)	0	1	ACEH	27.455786	4.625436
1	Other	Other	1.332341	109.374229	2012	examples/1.3323406178609702_109.37422873130464	train	POINT (109.37423 1.33234)	11	12	KALIMANTAN BARAT	39.643678	11.962830
2	Grassland shrubland	Grassland shrubland	-1.720266	115.006996	2016	examples/-1.720266384577504_115.00699582064485	train	POINT (115.00700 -1.72027)	13	14	KALIMANTAN TENGAH	23.242004	12.531139
3	Small-scale agriculture	Smallholder agriculture	-2.248346	104.135786	2011	examples/-2.248346072674411_104.1357857482906	train	POINT (104.13579 -2.24835)	32	33	SUMATERA SELATAN	20.464620	7.076179
4	Other large-scale plantations	Plantation	-2.100800	113.022376	2008	examples/-2.100800102991412_113.0223763234016	train	POINT (113.02238 -2.10080)	13	14	KALIMANTAN TENGAH	23.242004	12.531139

	label	merged_label	latitude	longitude	year	example_path	origin	province
0	Timber plantation	Plantation	4.430849	96.101634	2014	examples/4.430849118860583_96.1016343478138	train	aceh
1	Other	Other	1.332341	109.374229	2012	examples/1.3323406178609702_109.37422873130464	train	kalimantan barat
2	Grassland shrubland	Grassland shrubland	-1.720266	115.006996	2016	examples/-1.720266384577504_115.00699582064485	train	kalimantan tengah
3	Small-scale agriculture	Smallholder agriculture	-2.248346	104.135786	2011	examples/-2.248346072674411_104.1357857482906	train	sumatera selatan
4	Other large-scale plantations	Plantation	-2.100800	113.022376	2008	examples/-2.100800102991412_113.0223763234016	train	kalimantan tengah

	province	year	average_temp	average_rain
0	Aceh	2001	NaN	0.000
1	Bangka Belitung Islands	2001	26.933688	2757.200
2	Banten	2001	27.213151	2459.296
3	Bengkulu	2001	26.818207	2255.800
4	Central Kalimantan	2001	NaN	0.000

MSIN0166 Coursework Group 3¶

Data Engineering for Environmental Insight: Deforestation in Indonesia¶

Table of Contents¶

1. Introduction¶

1.1 Business Problem¶

1.2 Objective¶

2. Architecture¶

3. Extract Data¶

3.1 Forest Net Data¶

3.2 Regions Data¶

3.3 Weather Data¶

3.3.1 Integrating The API into the pipeline¶

3.3.2 Docker integration¶

4. Exploratory Data Analysis¶

4.1 EDA on ForestNet¶

4.2 EDA on Weather Data¶

5. Load¶

5.1 Load Forest Net Image Data and Weather Data Into Bucket¶

5.2 Connecting Buckets to Postgres in GCP¶

6. Transform¶

6.1 Connect Postgres to Spark Pipeline for merging data¶

6.1.1 Connect to Postgres via Python Script¶

6.1.2 Import SparkSession and install JDBC Driver¶

6.1.3 Upload the merged CSV file to PostgreSQL¶

6.2 Alternate Method: Merge data in Postgres¶

7. Data Visualizations¶

8. Limitations¶

8.1 Exploratory Attempt: Integrating PostgreSQL with BigQuery¶

8.1.1 Create a dataset and table in Bigquery¶

8.1.2 Create connection profiles¶

8.1.3 Create the Datastream¶

8.2 Limitations for scalability¶

Source Version Control(SCM)¶

Project Management¶

Summary¶

References¶

Appendix¶

Appendix I: Selection of Uploaded Images¶